#encoding:utf-8
import pymysql
import time
import http.client
import jwt

from flask import Flask, request, render_template, make_response, jsonify, session
from random import choice, shuffle

app = Flask(__name__)
app.secret_key = 'thisismy2021course'
JWT_SALT = 'iv%x6xo7l7_u9bf_u!9#g#m*)*=ej@bek5)(@u3kh*72+unjv='

def sql_dorm():
    # conn = pymysql.connect(host='localhost', user="root", passwd="f5a10ef2dd4f5e90", database="userLogin")
    conn = pymysql.connect(host='dorm-db', user="root", passwd="123456", database="dormSys")
    cursor = conn.cursor()
    
    return conn, cursor

def sql_user():
    # conn = pymysql.connect(host='localhost', user="root", passwd="f5a10ef2dd4f5e90", database="userLogin")
    conn = pymysql.connect(host='user-db', user="root", passwd="123456", database="userSys")
    cursor = conn.cursor()
    
    return conn, cursor
    
def sql_order():
    # conn = pymysql.connect(host='localhost', user="root", passwd="f5a10ef2dd4f5e90", database="userLogin")
    conn = pymysql.connect(host='order-db', user="root", passwd="123456", database="orderSys")
    cursor = conn.cursor()
    
    return conn, cursor

@app.route("/", methods=['GET', 'POST'])
@app.route('/Page_DromList_index.html', methods=['GET', 'POST'])
def dormList():
    if request.method == "GET":
        return render_template("/Page_DromList_index.html")
 
        
@app.route('/showInfo', methods=['GET', 'POST'])
def returnInfo():
    if request.method == "POST":
        conn, cursor = sql_user()
        conn_dorm, cursor_dorm = sql_dorm()
        
        infoFromWeb = request.json
        token = infoFromWeb["token"]
        if not token:
            return make_response(jsonify({ 'stuID': '', 'stuName': '', 'stuSex': '', 'can_choose': '', 'buidInfo': '' , 'roomName': '', 'veriNum': '' }), 200)
        
        payload = jwt.decode(token, JWT_SALT, algorithms='HS256')
        if not payload:
            return make_response(jsonify({ 'stuID': '', 'stuName': '', 'stuSex': '', 'can_choose': '', 'buidInfo': '' , 'roomName': '', 'veriNum': '' }), 200)
        userID = int(payload['userID'])
        
        # 个人信息
        sql_selectInfo = "SELECT stuID, stuName, stuSex, can_choose, verifyNum FROM `usersInfo`, `verification` WHERE usersInfo.id=%d AND verification.uid=usersInfo.id" % userID
        cursor.execute(sql_selectInfo)
        stuInfo = cursor.fetchone()
        stuID = stuInfo[0]
        stuName = stuInfo[1]
        stuSex = '男' if stuInfo[2] == 1 else '女'
        can_choose = '可以选宿' if stuInfo[3] == 1 else '无法选宿'
        veriNum = stuInfo[4]
        roomName = '暂无'
        if can_choose == '无法选宿':
            sql_selectRoomID = "SELECT roomID FROM `userInDorm` WHERE uid = %d" % userID
            cursor.execute(sql_selectRoomID)
            roomID = cursor.fetchone()
            roomID = int(roomID[0])
            
            sql_selectRoomName = "SELECT buildingName, unitName, roomName FROM `dormsBuilding`, `dormsUnit`, `dormsRoom` WHERE \
                                 dormsRoom.id=%d AND dormsUnit.id=dormsRoom.unitID AND dormsBuilding.id=dormsUnit.buildingID" % roomID
            cursor_dorm.execute(sql_selectRoomName)
            roomBuildName, roomUnitName, roomSelfName = cursor_dorm.fetchone()
            
            roomName = ' '.join([roomBuildName, roomUnitName, roomSelfName])
            
        conn.close()
        cursor.close()
        
        # 选宿信息
        conn, cursor = sql_dorm()
        sql_selectUnit = "SELECT `buildingName`, `unitName` FROM `dormsBuilding`, `dormsUnit` \
                         WHERE dormsBuilding.id = dormsUnit.buildingID AND dormsUnit.id = any(SELECT `unitID` FROM `dormsRoom` WHERE allowedSex = %d)" % int(stuInfo[2])
        cursor.execute(sql_selectUnit)
        buidInfo = cursor.fetchall()
        
        return make_response(jsonify({ 'stuID': stuID, 'stuName': stuName, 'stuSex': stuSex, 'can_choose': can_choose, 'buidInfo': buidInfo , 'roomName': roomName, 'veriNum': veriNum}), 200)

@app.route('/searchNum', methods=['GET', 'POST'])
def getInfo():
    if request.method == "POST":
        choose = request.json
        buildChoosed = str(choose["value"])
        buildNum = buildChoosed[:3]
        unitName = buildChoosed[3:]
        token = choose["token"]
        
        payload = jwt.decode(token, JWT_SALT, algorithms='HS256')
        if not payload:
            return make_response(jsonify({ 'state': ['LOGERR', token] }), 200)
        userID = int(payload['userID'])
        
        conn, cursor = sql_dorm()
        
        sql_roomNumRemain = "SELECT sum(remainNum) FROM `dormsRoom` \
                            WHERE unitID=any(SELECT id FROM `dormsUnit` WHERE unitName=\'%s\' AND buildingID=(SELECT id FROM `dormsBuilding` \
                            WHERE buildingName=\'%s\'))" % (unitName, buildNum)
        cursor.execute(sql_roomNumRemain)
        totalNum = cursor.fetchone()
        
        return make_response(jsonify({ 'totalNum': totalNum }), 200)


def testCheckOut(userID, stuID, stuVeri, conn, cursor):
    sql_userInfo = "SELECT stuID, stuSex, can_choose FROM `usersInfo` WHERE id=%d" % int(userID)
    cursor.execute(sql_userInfo)
    userInfo_fromSQL = cursor.fetchone()
    userStuID, userSex, user_can_choose = userInfo_fromSQL[0], userInfo_fromSQL[1], userInfo_fromSQL[2]
    
    sql_stuIDInfo = "SELECT id FROM `usersInfo` WHERE stuID=%d" % int(stuID)
    cursor.execute(sql_stuIDInfo)
    stuIDInfo_fromSQL = cursor.fetchone()
    
    if not stuIDInfo_fromSQL:
        return { 'success': '', 'error': 'NOTIN', 'stuName': ''}  # 未查到该stuID
    
    if str(user_can_choose) == '0':
        return { 'success': '', 'error': 'CHECERR', 'stuName': ''}  # 存在无法选宿舍的用户
        
    stuID = stuIDInfo_fromSQL[0]
    sql_stuInfo = "SELECT stuName, stuSex, can_choose, verifyNum FROM `usersInfo`, `verification` \
                  WHERE usersInfo.id=%d AND verification.uid=%d" % (int(stuID), int(stuID))
    cursor.execute(sql_stuInfo)
    stuInfo_fromSQL = cursor.fetchone()
    
    stuName, stuSex, stu_can_choose, verifyNum = stuInfo_fromSQL[0], stuInfo_fromSQL[1], stuInfo_fromSQL[2], stuInfo_fromSQL[3]
    
    if userStuID == stuID:
        return { 'success': '', 'error': 'SAMEERR', 'stuName': stuName}  # 邀请自己错误
    elif stuVeri != verifyNum:
        return { 'success': '', 'error': 'VERIERR', 'stuName': stuName}  # 邀请码错误
    elif str(stu_can_choose) == '0':
        return { 'success': '', 'error': 'CANERR', 'stuName': stuName}  # 无资格选宿舍
    elif userSex != stuSex:
        return { 'success': '', 'error': 'SEXERR', 'stuName': stuName}  # 性别错误
    elif userSex == stuSex and str(stu_can_choose) == '1' and stuVeri == verifyNum and userStuID != stuID:
        return { 'success': 'RIGHT', 'error': '', 'stuName': stuName}  # 成功
    else:
        return { 'success': '', 'error': 'ERROR', 'stuName': stuName}  # 未知错误

@app.route('/checkOut', methods=['GET', 'POST'])
def checkOut():
    if request.method == "POST":
        infoGetted = request.json
        token, stuID, stuVeri = infoGetted["token"], infoGetted["stuID"], infoGetted["stuVeri"]
        
        payload = jwt.decode(token, JWT_SALT, algorithms='HS256')
        if not payload:
            return make_response(jsonify({ 'state': ['LOGERR', token] }), 200)
            
        userID = int(payload['userID'])
        conn, cursor = sql_user()
        
        reutrnDict = testCheckOut(userID, stuID, stuVeri, conn, cursor)
        
        return make_response(jsonify(reutrnDict), 200)  # 未查到该stuID

def updateSQL(roomNum, userIDList, userSex, conn_user, cursor_user, conn_dorm, cursor_dorm, conn_order, cursor_order, bjTime):
    try:
        for userID in userIDList:
            sql_updateRoomInfo = "UPDATE dormsRoom SET remainNum=remainNum-1 where id=%d" % int(roomNum)  # 更新dorm表中数据
            cursor_dorm.execute(sql_updateRoomInfo)
            conn_dorm.commit()
            
            sql_updateUserInDorm = "INSERT INTO userInDorm(uid, roomID, createTime) VALUES (%d, %d, \'%s\')" % (int(userID), int(roomNum), bjTime)
            cursor_user.execute(sql_updateUserInDorm)  # 更新userInDorm表中数据
            conn_user.commit()
            
            sql_updateUserInfo = "UPDATE usersInfo SET can_choose=0 WHERE id=%d" % int(userID)
            cursor_user.execute(sql_updateUserInfo)  # 更新usersInfo表中数据
            conn_user.commit()

        return 'SUCCESS'
        
    except:
        return 'SQLERR'

@app.route('/submitInfo', methods=['GET', 'POST'])
def submitInfo():
    if request.method == "POST":
        infoGetted = request.json
        stuList, buildChoosed, bjTime, token = infoGetted["stuArr"], str(infoGetted["value"]), infoGetted["bjTime"], infoGetted["token"] # type: list
        buildNum = buildChoosed[:3]
        unitName = buildChoosed[3:]
        
        payload = jwt.decode(token, JWT_SALT, algorithms='HS256')
        if not payload:
            return make_response(jsonify({ 'state': ['LOGERR', token] }), 200)
        userID = int(payload['userID'])
        
        conn_user, cursor_user = sql_user()
        conn_dorm, cursor_dorm = sql_dorm()
        conn_order, cursor_order = sql_order()
        
        countSuccess = 0
        if len(stuList) != 0:
            for oneStu in stuList:
                if testCheckOut(userID, oneStu[0], oneStu[1], conn_user, cursor_user)['success'] == 'RIGHT':
                    countSuccess += 1
                    
        stuIDList = list()
        stuIDList.append(int(userID))
        stuAllNum = countSuccess + 1
        for oneStu in stuList:
            sql_selectID = "SELECT id FROM `usersInfo` WHERE stuID=\'%s\'" % oneStu[0]
            cursor_user.execute(sql_selectID)
            stuOfID = cursor_user.fetchone()
            stuIDList.append(int(stuOfID[0]))
        
        sql_sex = "SELECT stuSex, can_choose FROM `usersInfo` WHERE id=%d" % int(userID)
        cursor_user.execute(sql_sex)
        userSexFromSQL = cursor_user.fetchone()
        userSex, user_can_choose = int(userSexFromSQL[0]), int(userSexFromSQL[1])
        if user_can_choose == 0:
            # 插入 order 数据库错误，需要进行更改 #
            sql_updateOrders = "INSERT INTO orders(uid, buildID, togetherNum, is_success, submitTime, sex) \
                                VALUES (%d, \'%s\', \'%s\', %d,\'%s\', %d)" % (int(userID), -1, -1, -1, bjTime, int(userSex))
            cursor_order.execute(sql_updateOrders)  # 更新orders表中数据
            conn_order.commit()
            
            return make_response(jsonify({ 'state': 'CHECERR' }), 200)
        
        if len(stuList) == countSuccess:
            # - - - - - - - 插入 - - - - - - - #
            if countSuccess == 0:  # 单人选宿舍
                try:
                    sql_roomNumRemain = "SELECT id FROM `dormsRoom` \
                        WHERE unitID=any(SELECT id FROM `dormsUnit` WHERE unitName=\'%s\' AND buildingID=(SELECT id FROM `dormsBuilding` \
                        WHERE buildingName=\'%s\')) AND remainNum >= 1" % (unitName, buildNum)
                    cursor_dorm.execute(sql_roomNumRemain)
                    avaiableRooms = cursor_dorm.fetchall()
                    selectedRoomID = choice(avaiableRooms)  # 该学生最后选到的宿舍的ID为 selectedRoomID
                    
                    state = updateSQL(selectedRoomID[0], [userID], userSex, conn_user, cursor_user, conn_dorm, cursor_dorm, conn_order, cursor_order, bjTime)
                    
                    sql_updateOrders = "INSERT INTO orders(uid, buildID, togetherNum, is_success, submitTime, sex) \
                                        VALUES (%d, \'%s\', \'%s\', %d,\'%s\', %d)" % (int(userID), selectedRoomID, 1, 1 if state == 'SUCCESS' else 0, bjTime, int(userSex))
                    cursor_order.execute(sql_updateOrders)  # 更新orders表中数据
                    conn_order.commit()
                    
                    return make_response(jsonify({ 'state': state }), 200)
                except:
                    
                    sql_updateOrders = "INSERT INTO orders(uid, buildID, togetherNum, is_success, submitTime, sex) \
                                        VALUES (%d, \'%s\', \'%s\', %d,\'%s\', %d)" % (int(userID), -1, 1, 0, bjTime, int(userSex))
                    cursor_order.execute(sql_updateOrders)  # 更新orders表中数据
                    conn_order.commit()
                    return make_response(jsonify({ 'state': 'FULL' }), 200)
                
            else:  # 多人绑定选宿舍
                # 1. 返回多人的id: list
                shuffle(stuIDList)
                    
                # 2. 查找合适的宿舍
                # 2.1 如果存在人数 >= stuAllNum的房间，则返回一系列这样的房间号;
                sql_roomNumRemain = "SELECT id FROM `dormsRoom` \
                    WHERE unitID=any(SELECT id FROM `dormsUnit` WHERE unitName=\'%s\' AND buildingID=(SELECT id FROM `dormsBuilding` \
                    WHERE buildingName=\'%s\')) AND remainNum >= %d" % (unitName, buildNum, int(stuAllNum))
                cursor_dorm.execute(sql_roomNumRemain)
                avaiableRooms = cursor_dorm.fetchall()
                
                if len(avaiableRooms) >= 1:
                    selectedRoomID = choice(avaiableRooms)
                    
                    state = updateSQL(selectedRoomID[0], stuIDList, userSex, conn_user, cursor_user, conn_dorm, cursor_dorm, conn_order, cursor_order, bjTime)
                    
                    sql_updateOrders = "INSERT INTO orders(uid, buildID, togetherNum, is_success, submitTime, sex) \
                                        VALUES (%d, \'%s\', \'%s\', %d,\'%s\', %d)" % (int(userID), selectedRoomID, stuIDList, 1 if state == 'SUCCESS' else 0, bjTime, int(userSex))
                    cursor_order.execute(sql_updateOrders)  # 更新orders表中数据
                    conn_order.commit()
                    return make_response(jsonify({ 'state': state }), 200)
                else:
                    # 2.2 如果不存在, 则先判断剩余房间人数是否够用，不够返回选宿舍失败;
                    sql_roomNumRemain = "SELECT sum(remainNum) FROM `dormsRoom` \
                        WHERE unitID=any(SELECT id FROM `dormsUnit` WHERE unitName=\'%s\' AND buildingID=(SELECT id FROM `dormsBuilding` \
                        WHERE buildingName=\'%s\'))" % (unitName, buildNum)
                    cursor.execute(sql_roomNumRemain)
                    totalNum = cursor.fetchone()
                    
                    if int(totalNum[0]) < stuAllNum:
                        sql_updateOrders = "INSERT INTO orders(uid, buildID, togetherNum, is_success, submitTime, sex) \
                                            VALUES (%d, \'%s\', \'%s\', %d,\'%s\', %d)" % (int(userID), '-1', stuIDList, 0, bjTime, int(userSex))
                        cursor_order.execute(sql_updateOrders)  # 更新orders表中数据
                        conn_order.commit()
                        
                        return make_response(jsonify({ 'state': 'FULL' }), 200)
                    
                    else:
                        # 2.3 如果够用，依次返回有 stuAllNum-1, stuAllNum-2 ... 1 人的列表
                        idx = 0
                        roomIDList = list()
                        while stuAllNum > 0:
                            sql_roomNumRemain = "SELECT id, remainNum FROM `dormsRoom` \
                                WHERE unitID=any(SELECT id FROM `dormsUnit` WHERE unitName=\'%s\' AND buildingID=(SELECT id FROM `dormsBuilding` \
                                WHERE buildingName=\'%s\')) AND remainNum <= %d ORDER BY remainNum DESC" % (unitName, buildNum, int(stuAllNum))
                            cursor_dorm.execute(sql_roomNumRemain)
                            avaiableRooms = cursor_dorm.fetchall()
                            
                            selectedRoomID, selectNum = int(avaiableRooms[idx][0]), int(avaiableRooms[idx][1])
                            if stuAllNum - selectNum >= 0:
                                stuAllNum -= selectNum
                                stuIDReadyToSelcList = [stuIDList.pop() for _ in range(selectNum)]
                                roomIDList.appen(selectedRoomID)
                                state_1 = updateSQL(selectedRoomID, stuIDReadyToSelcList, userSex, conn_user, cursor_user, conn_dorm, cursor_dorm, conn_order, cursor_order, bjTime)
                                
                            elif stuAllNum <= selectNum:
                                stuAllNum = 0
                                roomIDList.appen(selectedRoomID)
                                state_2 = updateSQL(selectedRoomID, stuIDList, userSex, conn_user, cursor_user, conn_dorm, cursor_dorm, conn_order, cursor_order, bjTime)

                            else:
                                idx += 1
                                continue
                        sql_updateOrders = "INSERT INTO orders(uid, buildID, togetherNum, is_success, submitTime, sex) \
                                            VALUES (%d, \'%s\', \'%s\', %d,\'%s\', %d)" % (int(userID), roomIDList, stuIDList, 1, bjTime, int(userSex))
                        cursor_order.execute(sql_updateOrders)  # 更新orders表中数据
                        conn_order.commit()    
                        return make_response(jsonify({ 'state': 'SUCCESS' if state_1 == state_2 and state_1 == 'SUCCESS' else 'ERROR' }), 200)
                    
        else:
            sql_updateOrders = "INSERT INTO orders(uid, buildID, togetherNum, is_success, submitTime, sex) \
                                VALUES (%d, %d, %d, %d,\'%s\', %d)" % (int(userID), -1, stuIDList, 0, bjTime, int(userSex))
            cursor_order.execute(sql_updateOrders)  # 更新orders表中数据
            conn_order.commit()
            
            return make_response(jsonify({ 'state': 'STUERR' }), 200)

@app.route('/logout', methods=['GET', 'POST'])
def logout():
    if request.method == "POST":
        conn, cursor = sql_user()
        
        choose = request.json
        token = choose["token"]
        
        payload = jwt.decode(token, JWT_SALT, algorithms='HS256')
        if not payload:
            return make_response(jsonify({ 'state': ['LOGERR', token] }), 200)
        userID = int(payload['userID'])

        return make_response(jsonify({ 'msg': '退出成功！' }), 200)

app.run(host='0.0.0.0', port=3355)